Release 10.1A: OpenEdge Development:
Progress Dynamics Advanced Development

Using addQueryWhere and assignQuerySelection

Use the addQueryWhere function and the assignQuery selection function to build more complex and efficient queries.

addQueryWhere

The addQueryWhere function takes three INPUT parameters:

The new WHERE clause to be added to the existing one.
An optional buffer name specifying which buffer in a join to add the WHERE clause.
An optional operator to connect multiple WHERE clause fragments passed in successive calls. The default is AND, but you can also specify OR.

If you do not specify the buffer name parameter, then you must qualify the field names in your WHERE clause with their table names in order to allow the function to build the WHERE clause most efficiently, with each phrase appended to the query join clause where that table appears in the join sequence. The SDO property QueryString stores successive changes to the WHERE clause. This property is stored in the client SDO (if the SDO is split between client and server). When openQuery is run, it checks to see if the QueryString property has a value, and if so, sends it to the server, prepares the database query using that value, and then opens the query. This allows your code to build up a complex WHERE clause efficiently, without preparing or opening the intermediate steps (or even sending them to the server) until the signal is given to open the database query.

For example, if you create a new SmartWindow called waddwhere.w, you can use the addQueryWhere function to allow filtering of a query. To show how the function operates on a joined query, use a SmartDataObject called djoin.w, which joins “EACH Order, Customer OF Order, SalesRep OF Order”. There is a field called Where-Field in the window that you can use to enter a new WHERE clause phrase, with this trigger code:

/* ON LEAVE OF Where-Field */ DO: IF Where-Field:SCREEN-VALUE NE '' then DO: DYNAMIC-FUNCTION('addQueryWhere' IN h_djoin, Where-Field:SCREEN-VALUE, '',''). Editor-1:SCREEN-VALUE IN FRAME {&FRAME-NAME} = DYNAMIC-FUNCTION('getQueryString' IN h_djoin). END. END.

AssignQuerySelection and removeQuerySelection

The assignQuerySelection and removeQuerySelection functions are designed to make it easy to map values to fields to create a WHERE clause. These functions are especially suited to application procedures, such as a Query By Form, where you enter a value for one or more fields and expect to see database records matching those values.

The first of these functions, assignQuerySelection, takes three INPUT parameters:

A comma-separated list of fieldnames to be used in the WHERE clause.
A CHR(1)-delimited list of values for those fields.
An operator or list of operators to apply to the values. If this third parameter is not specified, EQUALS is the default. If a single operator is supplied for that parameter, it is applied to all field-value pairs. The special value of EQ/BEGINS means that numeric fields should use the EQ operator, and CHARACTER fields the BEGINS operator. Or a comma-separated list of operators can be supplied, and each operator in the list is applied to the corresponding field-value pair.

For example, you can use a SmartWindow called wselection.w to apply these functions to a Customer SDO. You can enter a list of fields in the Column-Field, and you can enter a list of values in the Values-Field. To simplify the example enter the values as a comma-separated list and replace the commas with CHR(1) when you use the list. Enter an operator in the third field. The leave trigger puts the pieces together and opens the SDO query with the values specified, as shown:

/* ON LEAVE OF Operator-Field */ DO: DYNAMIC-FUNCTION('assignQuerySelection' IN h_dcust, Column-Field:SCREEN-VALUE, REPLACE(Values-Field:SCREEN-VALUE,",",CHR(1)), Operator-Field:SCREEN-VALUE). EDITOR-2:SCREEN-VALUE = DYNAMIC-FUNCTION('getQueryString' IN h_dcust). DYNAMIC-FUNCTION('openQuery' IN h_dcust). END.

There is also a Remove-Field, which allows you to name a field that contains a WHERE clause fragment that you want to remove. Remove-Field executes the removeQuerySelection function, which takes two INPUT parameters: the name of the field and the operator associated with that field, as shown:

/* ON CHOOSE OF Remove-Field */ DO: DYNAMIC-FUNCTION('removeQuerySelection' IN h_dcust, Remove-Field:SCREEN-VALUE, Operator-Field:SCREEN-VALUE). EDITOR-2:SCREEN-VALUE = DYNAMIC-FUNCTION('getQueryString' IN h_dcust). DYNAMIC-FUNCTION('openQuery' IN h_dcust). END.

You can test how these functions work by entering two field names, values for those fields, and an operator.

To test this window:

Enter name,address for the Columns field.
Enter L,2 for the Values field.
Enter BEGINS for the Operator field.

The browse now displays all the customers that match those values, as shown:

Note that one feature of the assignQuerySelection function is that it handles quotation marks properly. The values entered into the list must not be in quotation marks. It is easier to assemble a list of values that come out of a list of fill-in fields for different database fields without having to add code to put quotes around those that need it (CHARACTER and DATE types); assignQuerySelection does this for you. Note that assignQuerySelection also identifies the table that each field comes from and qualifies the field name references. If there is a join involved, it also distributes the where clause phrases properly for maximum efficiency.

Typing Address into the Remove field runs removeQuerySelection, which removes that part of the WHERE clause from the query, as shown: